package zjc.bookkeeping.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.sql.Time;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import zjc.bookkeeping.tools.ToolsClass;

public class DBManager {
    private static SQLiteDatabase db;
    public static void initDB(Context context){
        DBOpenHelper helper = new DBOpenHelper(context);
        db = helper.getWritableDatabase();
    }
//    根据分类查询收入和支出分类
    public static List<ConsumptionClassBean> getConsumptionClass(int kind){
        List<ConsumptionClassBean> list = new ArrayList<>();
        String sql = sql = "select * from consumptionclass where consumption_class_kind = "+kind;
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()){
            int consumption_class_id = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_class_id"));
            String consumption_class_name = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_name"));
            String consumption_class_image = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_image"));
            ConsumptionClassBean consumptionClassBean = new ConsumptionClassBean(consumption_class_id, consumption_class_name, consumption_class_image, kind);
            list.add(consumptionClassBean);
        }
        cursor.close();
        return list;
    }
    //新增收入和支出分类
    public static void insertConsumptionClass(ConsumptionClassBean bean){
        ContentValues values = new ContentValues();
        values.put("consumption_class_name",bean.getConsumption_class_name());
        if (bean.getConsumption_class_image() != null){
            values.put("consumption_class_image",bean.getConsumption_class_image());
        }
        values.put("consumption_class_kind",bean.getConsumption_class_kind());
        db.insert("consumptionclass",null,values);
        Log.i("succeed","name: "+bean.getConsumption_class_name()+"   image: "+bean.getConsumption_class_image()+"   kind: "+bean.getConsumption_class_kind()+"插入数据成功！！！");
    }
    //    修改指定id消费分类
    public static void updateConsumptionClassbyid(ConsumptionClassBean bean) {
        String sql = "update consumptionclass set consumption_class_name = '"+ bean.getConsumption_class_name() +"',consumption_class_kind = '"+bean.getConsumption_class_kind()+"' where consumption_class_id =  '"+ bean.getConsumption_class_id()+"'";
        db.execSQL(sql);
        Log.i("succeed","name: "+bean.getConsumption_class_name()+"数据更改成功！！！");
    }
    //根据id删除消费分类
    public static void deleteConsumptionClassbyid(int mid) {
        String sql = "delete from consumptionclass where consumption_class_id = '" + mid+"'";
        db.execSQL(sql);
    }
//    向记账表新增一次记录
    public static void insertConsumption(ConsumptionBean bean){
        ContentValues values = new ContentValues();
        values.put("consumption_class_name",bean.getConsumption_class_name());
        values.put("consumption_remarks",bean.getConsumption_remarks());
        values.put("consumption_money",bean.getConsumption_money());
        values.put("consumption_business",bean.getConsumption_business());
        values.put("account_id",bean.getaccount_id());
        values.put("consumption_method",bean.getConsumption_method());
        values.put("consumption_time",bean.getConsumption_time());
        values.put("consumption_year",bean.getConsumption_year());
        values.put("consumption_month",bean.getConsumption_month());
        values.put("consumption_day",bean.getConsumption_day());
        values.put("consumption_kind",bean.getConsumption_kind());
        values.put("consumption_key",bean.getConsumption_key());
        db.insert("consumption",null,values);
        Log.i("succeed","name: "+bean.getConsumption_class_name()+"   remarks: "+bean.getConsumption_remarks()
                +"   money: "+bean.getConsumption_money()+"   business: "+bean.getConsumption_business()+"   account: "+bean.getaccount_id()
                +"   method: "+bean.getConsumption_method()+"   time: "+bean.getConsumption_time()+"   kind: "+bean.getConsumption_kind()+"   key:"+bean.getConsumption_key()+"插入数据成功！！！");
    }
//    获取本账本所有记录
    public static List<ConsumptionBean> getAllAccountConsumption(String account_id, String consumptionName, String time){
        List<ConsumptionBean> list = new ArrayList<>();
        String sql;
        switch (consumptionName){
            case "全部":
                sql = "select * from consumption where account_id = '"+ account_id +
                        "' and consumption_time like '" + time +
                        "%' order by consumption_time DESC";
                break;
            case "支出":
                sql = "select * from consumption where account_id = '"+ account_id +
                        "' and consumption_kind = " + 0 +
                        " and consumption_time like '" + time +
                        "%' order by consumption_time DESC";
                break;
            case "收入":
                sql = "select * from consumption where account_id = '"+ account_id +
                        "' and consumption_kind = " + 1 +
                        " and consumption_time like '" + time +
                        "%' order by consumption_time DESC";
                break;
            case "转账":
                sql = "select * from consumption where account_id = '"+ account_id +
                        "' and consumption_kind = " + 2 +
                        " and consumption_time like '" + time +
                        "%' order by consumption_time DESC";
                break;
            default:
                sql = "select * from consumption where account_id = '"+ account_id +
                        "' and consumption_class_name = '" + consumptionName +
                        "' and consumption_time like '" + time +
                        "%' order by consumption_time DESC";
        }
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int consumption_id = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_id"));
            String consumption_class_name = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_name"));
            String consumption_remarks = cursor.getString(cursor.getColumnIndexOrThrow("consumption_remarks"));
            float consumption_money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            String consumption_business = cursor.getString(cursor.getColumnIndexOrThrow("consumption_business"));
            String consumption_method = cursor.getString(cursor.getColumnIndexOrThrow("consumption_method"));
            String consumption_time = cursor.getString(cursor.getColumnIndexOrThrow("consumption_time"));
            int consumption_year = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_year"));
            int consumption_month = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_month"));
            int consumption_day = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_day"));
            int consumption_kind = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_kind"));
            int consumption_key = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_key"));
            ConsumptionBean consumptionBean = new ConsumptionBean(consumption_id,consumption_class_name,consumption_remarks,consumption_money,consumption_business,account_id,consumption_method,consumption_time,consumption_year,consumption_month,consumption_day,consumption_kind,consumption_key);
            list.add(consumptionBean);
        }
        cursor.close();
        return list;
    }
    //    获取本账指定资产账户所有记录
    public static List<ConsumptionBean> getAllAccountConsumptionbyaccount(String consumptionMethod, String time){
        List<ConsumptionBean> list = new ArrayList<>();
        String sql = "select * from consumption where consumption_method = '" + consumptionMethod +"' and consumption_time like '" + time +
                        "%' order by consumption_time DESC";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int consumption_id = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_id"));
            String consumption_class_name = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_name"));
            String consumption_remarks = cursor.getString(cursor.getColumnIndexOrThrow("consumption_remarks"));
            float consumption_money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            String consumption_business = cursor.getString(cursor.getColumnIndexOrThrow("consumption_business"));
            String account_id = cursor.getString(cursor.getColumnIndexOrThrow("account_id"));
            String consumption_method = cursor.getString(cursor.getColumnIndexOrThrow("consumption_method"));
            String consumption_time = cursor.getString(cursor.getColumnIndexOrThrow("consumption_time"));
            int consumption_year = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_year"));
            int consumption_month = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_month"));
            int consumption_day = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_day"));
            int consumption_kind = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_kind"));
            int consumption_key = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_key"));
            ConsumptionBean consumptionBean = new ConsumptionBean(consumption_id,consumption_class_name,consumption_remarks,consumption_money,consumption_business,account_id,consumption_method,consumption_time,consumption_year,consumption_month,consumption_day,consumption_kind,consumption_key);
            list.add(consumptionBean);
        }
        cursor.close();
        return list;
    }
    // 获取所有账本
    public static List<AccountBean> getAllAccount(){
        //总支出
        //String sqloutcome = "select sum(consumption_money) from consumption where consumption_kind=0";
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from account";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndexOrThrow("account_id"));
            String name = cursor.getString(cursor.getColumnIndexOrThrow("account_name"));
            Log.i("accountName",name+"");
            float outcome = 0;
            float income = 0;
            Cursor sqloutcome = db.rawQuery("select sum(consumption_money) from consumption where consumption_kind=0 and account_id='" + id + "'",null);
            while (sqloutcome.moveToNext()){
                outcome = sqloutcome.getFloat(sqloutcome.getColumnIndexOrThrow("sum(consumption_money)"));
            }
            sqloutcome.close();
            Cursor sqlincome = db.rawQuery("select sum(consumption_money) from consumption where consumption_kind=1 and account_id='" + id + "'",null);
            while (sqlincome.moveToNext()){
                income = sqlincome.getFloat(sqlincome.getColumnIndexOrThrow("sum(consumption_money)"));
            }
            sqlincome.close();
            int item = 0;
            Cursor sqlItem = db.rawQuery("select * from consumption where account_id='"+id+"'",null);
            while (sqlItem.moveToNext()){
                item++;
            }
            sqlItem.close();
            Log.i("item",item+"");
            String time = cursor.getString(cursor.getColumnIndexOrThrow("account_time"));
            int kind=cursor.getInt(cursor.getColumnIndexOrThrow("account_kind"));
            String key = cursor.getString(cursor.getColumnIndexOrThrow("account_key"));
//            Log.i("account_key",key+"");
            AccountBean accountBean = new AccountBean();
            accountBean.setAccount_id(id);
            accountBean.setAccount_name(name);
            accountBean.setAccount_outcome(outcome);
            accountBean.setAccount_income(income);
            accountBean.setAccount_item(item);
            accountBean.setAccount_time(time);
            accountBean.setAccount_kind(kind);
            accountBean.setAccount_key(key);
//            Log.i("key",accountBean.getAccount_key()+"");
            list.add(accountBean);
        }
        cursor.close();
        return  list;
    }

    //新增账本
    public static void insertAccount(String name,int i,String uid){
        ContentValues values = new ContentValues();
        values.put("account_name",name);
        Calendar cal_1=Calendar.getInstance();
        String time = ToolsClass.getYMDTime(0);
        values.put("account_time", time);
        values.put("account_kind",i);
        values.put("account_key",uid);
        db.insert("account",null,values);
        Log.i("succeed","name: "+name+"插入数据成功！！！");
    }
    //    修改指定id账本
    public static void updateAccountbyid(AccountBean bean) {
        String sql = "update account set account_key = '"+ bean.getAccount_key() +"' where account_id =  '"+ bean.getAccount_id()+"'";
        db.execSQL(sql);
        Log.i("succeed","name: "+bean.getAccount_key()+"数据更改成功！！！");
    }
    //根据id删除账本
    public static void deleteAccountbyid(int mid) {
        String sql = "delete from account where account_id = '" + mid+"'";
        db.execSQL(sql);
    }
//    获取所有商家
    public static List<String> getAllBusiness(){
        List<String> list = new ArrayList<>();
        String sql = "select business_name from business";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String business_name = cursor.getString(cursor.getColumnIndexOrThrow("business_name"));
            list.add(business_name);
        }
        cursor.close();
        return list;
    }
//    创建商家
    public static void createBusiness(String business_name){
        ContentValues values = new ContentValues();
        values.put("business_name",business_name);
        db.insert("business",null,values);
        Log.i("创建商家：<",business_name+">成功!");
    }
//    创建资金账户
    public static void createMethod(MethodBean bean){
        ContentValues values = new ContentValues();
        values.put("method_name",bean.getMethod_name());
        values.put("method_image",bean.getMethod_image());
        values.put("method_money", bean.getMethod_money());
        values.put("method_remark", bean.getMethod_remark());
        values.put("method_card", bean.getMethod_card());
        db.insert("method",null,values);
        Log.i("创建账户","账户名字："+ bean.getMethod_name()+"账户图片"+ bean.getMethod_image()+"账户金额"+bean.getMethod_money()+"账户备注"+bean.getMethod_remark()+"账户卡号"+bean.getMethod_card());
    }
//    查询所有账户
    public static List<MethodBean> getAllMethod(){
        List<MethodBean> list = new ArrayList<>();
        String sql = "select * from method";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndexOrThrow("method_id"));
            String name = cursor.getString(cursor.getColumnIndexOrThrow("method_name"));
            String image = cursor.getString(cursor.getColumnIndexOrThrow("method_image"));
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("method_money"));
            String remark = cursor.getString(cursor.getColumnIndexOrThrow("method_remark"));
            String card = cursor.getString(cursor.getColumnIndexOrThrow("method_card"));
            MethodBean methodBean = new MethodBean(id,name,image,money,remark,card);
            list.add(methodBean);
        }
        cursor.close();
        return  list;
    }

    // 查询资产总数
    public static float getAllMethodMoney(){
        String sql = "select method_money from method";
        Cursor cursor = db.rawQuery(sql,null);
        float sum = 0;
        while (cursor.moveToNext()){
            sum += cursor.getFloat(cursor.getColumnIndexOrThrow("method_money"));
        }
        cursor.close();
        return  sum;
    }
    //    查询指定id账户
    public static List<MethodBean> getMethodbyid(int uid){
        List<MethodBean> list = new ArrayList<>();
        String sql = "select * from method where method_id= '"+uid+"'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndexOrThrow("method_id"));
            String name = cursor.getString(cursor.getColumnIndexOrThrow("method_name"));
            String image = cursor.getString(cursor.getColumnIndexOrThrow("method_image"));
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("method_money"));
            String remark = cursor.getString(cursor.getColumnIndexOrThrow("method_remark"));
            String card = cursor.getString(cursor.getColumnIndexOrThrow("method_card"));
            MethodBean methodBean = new MethodBean(id,name,image,money,remark,card);
            list.add(methodBean);
        }
        cursor.close();
        return  list;
    }
    //    修改指定id账户的余额
    public static void updateMethodOutmonry(float money,int id) {
       // UPDATE method SET method_money=method_money-100 WHERE method_id=100000001
        String sql = "update method set method_money = method_money-'" + money +"'  where method_id =  '"+ id+"'";
        db.execSQL(sql);
        Log.i("succeed","数据更改成功！！！");
    }
    public static void updateMethodInmonry(float money,int id) {
        // UPDATE method SET method_money=method_money-100 WHERE method_id=100000001
        String sql = "update method set method_money = method_money+'" + money +"'  where method_id =  '"+ id+"'";
        db.execSQL(sql);
        Log.i("succeed","数据更改成功！！！");
    }
    //    修改指定id账户
        public static void updateMethodbyid(MethodBean methodBean) {
            String sql = "update method set method_name = '"+ methodBean.getMethod_name() +"' ,method_money = '" + methodBean.getMethod_money() +"' ,method_remark =  '"+ methodBean.getMethod_remark() +"' ,method_card =  '"+ methodBean.getMethod_card() +"'  where method_id =  '"+ methodBean.getMethod_id()+"'";
            db.execSQL(sql);
            Log.i("succeed","name: "+methodBean.getMethod_name()+"数据更改成功！！！");
        }
    //根据id删除账户
    public static void deleteMethodbyid(int mid) {
        String sql = "delete from method where method_id = '" + mid+"'";
        db.execSQL(sql);
    }
    //    根据账户名查询账户图片
    public static String getMethodImage(String name){
        String methodImage = null;
        String sql = "select method_image from method where method_name = '" + name + "'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            methodImage = cursor.getString(cursor.getColumnIndexOrThrow("method_image"));
        }
        cursor.close();
        return methodImage;
    }


    // 获取某日某一分类下的消费总数
    public static Float getDayClassConsumption(int year, int month, int day, String consumption_class_name){
        float sum = 0;
        String sql = "select consumption_money from consumption where consumption_year = " + year + " and consumption_month = "+ month + " and consumption_day = " + day + " and consumption_class_name = '" + consumption_class_name +"'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            sum += money;
        }
        cursor.close();
        return  sum;
    }

    // 获取某日某一分类下的消费笔数
    public static int getDayClassSum(int year, int month, int day, String consumption_class_name){
        String sql = "select * from consumption where consumption_year = " + year + " and consumption_month = "+ month + " and consumption_day = " + day + " and consumption_class_name = '" + consumption_class_name +"'";
        Cursor cursor = db.rawQuery(sql,null);
        int count = cursor.getCount();
        cursor.close();
        return count;
    }

    // 获取某月某一分类下的消费总数
    public static Float getMonthClassConsumption(int year, int month, String consumption_class_name){
        float sum = 0;
        String sql = "select consumption_money from consumption where consumption_year = " + year + " and consumption_month = "+ month + " and consumption_class_name = '" + consumption_class_name +"'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            sum += money;
        }
        cursor.close();
        return  sum;
    }

    // 获取每个月的收入/支出总数
    public static Float getMonthConsumption(int year, int month, int kind){
        float sum = 0;
        String sql = "select consumption_money from consumption where consumption_year = " + year + " and consumption_month = "+ month +" and consumption_kind = " + kind;
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            sum += money;
        }
        cursor.close();
        return  sum;
    }

    // 获取某年某一分类下的消费总数
    public static Float getYearClassConsumption(int year, String consumption_class_name){
        float sum = 0;
        String sql = "select consumption_money from consumption where consumption_year = " + year + " and consumption_class_name = '" + consumption_class_name +"'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            float money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            sum += money;
        }
        cursor.close();
        return  sum;
    }


    // 获取所有消费分类
    public static List<String> getAllConsumptionClass(){
        List<String> classes = new ArrayList<>();
        String sql = "select consumption_class_name from consumptionclass where consumption_class_kind = 0";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String name = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_name"));
            classes.add(name);
        }
        cursor.close();
        return  classes;
    }
    // 按日查询 总和
    public static float getDaySumConsumption(int year, int month, int day, int kind){
        String sql = "select consumption_money from consumption where consumption_year = " + year +" and consumption_month = "+ month +" and consumption_day = " + day+" and consumption_kind = " + kind;
        //List<ConsumptionBean> list = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql,null);
        float sum = 0;
        while (cursor.moveToNext()){
            float consumption_money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            sum += consumption_money;
        }
        cursor.close();
        return sum;
    }

    // 获取年支出/收入
    public static float getYearSumConsumption(int year, int kind){
        String sql = "select consumption_money from consumption where consumption_year = " + year + " and consumption_kind = " + kind;
        Cursor cursor = db.rawQuery(sql,null);
        float sum = 0;
        while (cursor.moveToNext()){
            float consumption_money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            sum += consumption_money;
        }
        cursor.close();
        return sum;
    }

//    查询所有分类
    public  static List<String> getAllConsumptionClassName(){
        List<String> list = new ArrayList<>();
        String sql = "select * from consumptionclass";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String name = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_name"));
            list.add(name);
        }
        cursor.close();
        return list;
    }
//    查询一段时间记录
    public static List<ConsumptionBean> getSpanConsumptionBean(String accountName, String nowTime, String oldTime) {
        List<ConsumptionBean> list = new ArrayList<>();
        String sql = "select * from consumption where account_id = '"+ accountName +
                "' and consumption_time between '" + oldTime +
                "' and '" + nowTime +
                "' order by consumption_time DESC";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int consumption_id = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_id"));
            String consumption_class_name = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_name"));
            String consumption_remarks = cursor.getString(cursor.getColumnIndexOrThrow("consumption_remarks"));
            float consumption_money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            String consumption_business = cursor.getString(cursor.getColumnIndexOrThrow("consumption_business"));
            String consumption_method = cursor.getString(cursor.getColumnIndexOrThrow("consumption_method"));
            String consumption_time = cursor.getString(cursor.getColumnIndexOrThrow("consumption_time"));
            int consumption_year = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_year"));
            int consumption_month = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_month"));
            int consumption_day = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_day"));
            int consumption_kind = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_kind"));
            int consumption_key = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_key"));
            ConsumptionBean consumptionBean = new ConsumptionBean(consumption_id,consumption_class_name,consumption_remarks,consumption_money,consumption_business,accountName,consumption_method,consumption_time,consumption_year,consumption_month,consumption_day,consumption_kind,consumption_key);
            list.add(consumptionBean);
        }
        cursor.close();
        return list;
    }
//    根据消费分类获取分类名
    public static List<String> getConsumptionClassName(int kind) {
        List<String> list = new ArrayList<>();
        String sql = "select * from consumptionclass where consumption_class_kind = "+kind;
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()){
            String consumption_class_name = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_name"));
            list.add(consumption_class_name);
        }
        cursor.close();
        return list;
    }
//根据id删除账单数据
    public static void deleteConsumption(int consumption_id) {
        String sql = "delete from consumption where consumption_id = " + consumption_id;
        db.execSQL(sql);
    }
//获取所有账本名字
    public static List<String> getAllAccountName() {
        List<String> list = new ArrayList<>();
        String sql = "select account_name from account";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            String account_name = cursor.getString(cursor.getColumnIndexOrThrow("account_name"));
            list.add(account_name);
        }
        cursor.close();
        return list;
    }
//更改一条账本记录的信息
    public static void updateConsumption(ConsumptionBean consumptionBean) {
        String sql = "update consumption set consumption_class_name ='" + consumptionBean.getConsumption_class_name() +
                "',consumption_remarks = '" + consumptionBean.getConsumption_remarks() +
                "',consumption_money = " + consumptionBean.getConsumption_money() +
                ",consumption_business = '" + consumptionBean.getConsumption_business() +
                "',account_id = '" + consumptionBean.getaccount_id() +
                "',consumption_method = '" + consumptionBean.getConsumption_method() +
                "',consumption_time = '" + consumptionBean.getConsumption_time() +
                "',consumption_year = " + consumptionBean.getConsumption_year() +
                ",consumption_month = " + consumptionBean.getConsumption_month() +
                ",consumption_day = " + consumptionBean.getConsumption_day() +
                " where consumption_id = " + consumptionBean.getConsumption_id();
        db.execSQL(sql);
        Log.i("succeed","name: "+consumptionBean.getConsumption_class_name()+"   remarks: "+consumptionBean.getConsumption_remarks()
                +"   money: "+consumptionBean.getConsumption_money()+"   business: "+consumptionBean.getConsumption_business()+"   account: "+consumptionBean.getaccount_id()
                +"   method: "+consumptionBean.getConsumption_method()+"   time: "+consumptionBean.getConsumption_time()+"   kind: "+consumptionBean.getConsumption_kind()+"数据更改成功！！！");
    }
//根据分类名获取图片名
    public static String getConsumptionClassImage(String name) {
        String imageName = null;
        String sql = "select * from consumptionclass where consumption_class_name = '"+name + "'";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()){
            imageName = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_image"));
        }
        return imageName;
    }

//    //查询用户是否已注册
//    //根据手机号查询
//    public static List<UserBean> getUserbyphone(String phone){
//        List<UserBean> list=new  ArrayList<>();
//        String sql = "SELECT * from user where user_photo='"+phone+"'";
//        Cursor cursor = db.rawQuery(sql,null);
//        while (cursor.moveToNext()){
//            int user_id = cursor.getInt(cursor.getColumnIndexOrThrow("user_id"));
//            String user_name=cursor.getString(cursor.getColumnIndexOrThrow("user_name"));
//            String user_photo=cursor.getString(cursor.getColumnIndexOrThrow("user_photo"));
//            String user_phone=cursor.getString(cursor.getColumnIndexOrThrow("user_phone"));
//            String user_qq=cursor.getString(cursor.getColumnIndexOrThrow("user_qq"));
//            String user_wx=cursor.getString(cursor.getColumnIndexOrThrow("user_wx"));
//            String user_zfb=cursor.getString(cursor.getColumnIndexOrThrow("user_zfb"));
//            UserBean userBean = new UserBean(user_id,user_name,user_photo,user_phone,user_qq,user_wx,user_zfb);
//            list.add(userBean);
//        }
//        return list;
//    }
//    //根据qq好查询
//    public static List<UserBean> getUserbyqq(int qq){
//        List<UserBean> list=new  ArrayList<>();
//        String sql = "SELECT * from user where user_qq='"+qq+"'";
//        Cursor cursor = db.rawQuery(sql,null);
//        while (cursor.moveToNext()){
//            int user_id = cursor.getInt(cursor.getColumnIndexOrThrow("user_id"));
//            String user_name=cursor.getString(cursor.getColumnIndexOrThrow("user_name"));
//            String user_photo=cursor.getString(cursor.getColumnIndexOrThrow("user_photo"));
//            int user_phone=cursor.getInt(cursor.getColumnIndexOrThrow("user_phone"));
//            String user_qq=cursor.getString(cursor.getColumnIndexOrThrow("user_qq"));
//            String user_wx=cursor.getString(cursor.getColumnIndexOrThrow("user_wx"));
//            String user_zfb=cursor.getString(cursor.getColumnIndexOrThrow("user_zfb"));
//            UserBean userBean = new UserBean(user_id,user_name,user_photo,user_phone,user_qq,user_wx,user_zfb);
//            list.add(userBean);
//        }
//        return list;
//    }
//    //根据微信号查询
//    public static List<UserBean> getUserbywx(String wx){
//        List<UserBean> list=new  ArrayList<>();
//        String sql = "SELECT * from user where user_wx='"+wx+"'";
//        Cursor cursor = db.rawQuery(sql,null);
//        while (cursor.moveToNext()){
//            int user_id = cursor.getInt(cursor.getColumnIndexOrThrow("user_id"));
//            String user_name=cursor.getString(cursor.getColumnIndexOrThrow("user_name"));
//            String user_photo=cursor.getString(cursor.getColumnIndexOrThrow("user_photo"));
//            int user_phone=cursor.getInt(cursor.getColumnIndexOrThrow("user_phone"));
//            String user_qq=cursor.getString(cursor.getColumnIndexOrThrow("user_qq"));
//            String user_wx=cursor.getString(cursor.getColumnIndexOrThrow("user_wx"));
//            String user_zfb=cursor.getString(cursor.getColumnIndexOrThrow("user_zfb"));
//            UserBean userBean = new UserBean(user_id,user_name,user_photo,user_phone,user_qq,user_wx,user_zfb);
//            list.add(userBean);
//        }
//        return list;
//    }
//    //根据支付宝号查询
//    public static List<UserBean> getUserbyzfb(String zfb){
//        List<UserBean> list=new  ArrayList<>();
//        String sql = "SELECT * from user where user_zfb='"+zfb+"'";
//        Cursor cursor = db.rawQuery(sql,null);
//        while (cursor.moveToNext()){
//            int user_id = cursor.getInt(cursor.getColumnIndexOrThrow("user_id"));
//            String user_name=cursor.getString(cursor.getColumnIndexOrThrow("user_name"));
//            String user_photo=cursor.getString(cursor.getColumnIndexOrThrow("user_photo"));
//            int user_phone=cursor.getInt(cursor.getColumnIndexOrThrow("user_phone"));
//            String user_qq=cursor.getString(cursor.getColumnIndexOrThrow("user_qq"));
//            String user_wx=cursor.getString(cursor.getColumnIndexOrThrow("user_wx"));
//            String user_zfb=cursor.getString(cursor.getColumnIndexOrThrow("user_zfb"));
//            UserBean userBean = new UserBean(user_id,user_name,user_photo,user_phone,user_qq,user_wx,user_zfb);
//            list.add(userBean);
//        }
//        return list;
//    }

    //新增用户
    public static void insertUser(UserBean bean){
        ContentValues values = new ContentValues();
        values.put("user_id",bean.getUser_id());
        values.put("user_name",bean.getUser_name());
        values.put("user_photo",bean.getUser_photo());
        values.put("user_phone",bean.getUser_phone());
        values.put("user_qq",bean.getUser_qq());
        values.put("user_wx",bean.getUser_wx());
        values.put("user_zfb",bean.getUser_zfb());
        db.insert("user",null,values);
        Log.i("succeed","id" + bean.getUser_id() + "name: "+bean.getUser_name()+"   photo: "+bean.getUser_photo()
                +"   phone: "+bean.getUser_phone()+"   qq: "+bean.getUser_qq()+"   wx: "+bean.getUser_wx()
                +"   zfb: "+bean.getUser_zfb()+"插入数据成功！！！");
    }
    //查询是否存在本月数据
    public static boolean findQuota(String time) {
        boolean exitFlag = false;
        String sql = "SELECT * from quota where quota_time = '"+time+"'";
        Cursor cursor = db.rawQuery(sql,null);
        if (cursor == null){
            exitFlag = true;
            return exitFlag;
        }
        cursor.close();
        return exitFlag;
    }
    //创建本月消费预算记录
    public static void createQuota(String ymTime) {
        ContentValues values = new ContentValues();
        values.put("quota_money",1500);
        values.put("quota_time",ymTime);
        db.insert("quota",null,values);
    }
    //获取本月消费预算
    public static float getQuotaMoney(String ymTime) {
        float money = 0;
        String sql = "SELECT * from quota where quota_time = '"+ymTime+"'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            money = cursor.getFloat(cursor.getColumnIndexOrThrow("quota_money"));
        }
        cursor.close();
        return money;
    }
    //更改某月预算
    public static void updateQuota(float money, String ymTime) {
        String sql = "update quota set quota_money = " + money + " where quota_time = '" + ymTime + "'";
        db.execSQL(sql);
    }
    //查询本地储存的用户数据
    public static UserBean findUser() {
        UserBean userBean = null;
        String sql = "select * from user";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int user_id = cursor.getInt(cursor.getColumnIndexOrThrow("user_id"));
            String user_name=cursor.getString(cursor.getColumnIndexOrThrow("user_name"));
            String user_photo=cursor.getString(cursor.getColumnIndexOrThrow("user_photo"));
            String user_phone=cursor.getString(cursor.getColumnIndexOrThrow("user_phone"));
            String user_qq=cursor.getString(cursor.getColumnIndexOrThrow("user_qq"));
            String user_wx=cursor.getString(cursor.getColumnIndexOrThrow("user_wx"));
            String user_zfb=cursor.getString(cursor.getColumnIndexOrThrow("user_zfb"));
            userBean = new UserBean(user_id, user_name, user_photo, user_phone, user_qq, user_wx, user_zfb);
            Log.i("succeed","id" + userBean.getUser_id() + "name: "+userBean.getUser_name()+"   photo: "+userBean.getUser_photo()
                    +"   phone: "+userBean.getUser_phone()+"   qq: "+userBean.getUser_qq()+"   wx: "+userBean.getUser_wx()
                    +"   zfb: "+userBean.getUser_zfb()+"插入数据成功！！！");
        }
        cursor.close();
        return userBean;
    }
    //删除本地储存的用户数据
    public static void deleteUser() {
        String sql = "delete from user";
        db.execSQL(sql);
    }

    public static String getAccountName(int accountId) {
        String name = "";
        String sql = "select * from account where account_id=" + accountId;
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            name = cursor.getString(cursor.getColumnIndexOrThrow("account_name"));
        }
        cursor.close();
        return name;
    }
    //获取选择的账本Id
    public static int selectAccountId() {
        int accountId = 0;
        String sql = "select * from selectAccount";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            accountId = cursor.getInt(cursor.getColumnIndexOrThrow("accountId"));
        }
        cursor.close();
        return accountId;
    }
    //获取选择的账本Id
    public static int selectPos() {
        int selectPos = 0;
        String sql = "select * from selectAccount";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            selectPos = cursor.getInt(cursor.getColumnIndexOrThrow("selectPos"));
        }
        cursor.close();
        return selectPos;
    }
    //修改选择的账本
    public static void insertSelectAccount(int position, int account_id) {
        String sql = "delete from selectAccount";
        db.execSQL(sql);
        ContentValues values = new ContentValues();
        values.put("selectPos",position);
        values.put("accountId",account_id);
        db.insert("selectAccount",null,values);
    }

    public static void resetTable() {
        String sql = "delete from consumptionclass where consumption_class_id > 14";
        db.execSQL(sql);
        sql = "delete from account";
        db.execSQL(sql);
        sql = "UPDATE sqlite_sequence SET seq = 0 WHERE name = 'account'";
        db.execSQL(sql);
        sql = "insert into account (account_name,account_time,account_kind) values(?,?,?)";
        db.execSQL(sql, new Object[]{"日常账本",ToolsClass.getYMDTime(0),0});
        sql = "delete from consumption";
        db.execSQL(sql);
        sql = "UPDATE sqlite_sequence SET seq = 0 WHERE name = 'consumption'";
        db.execSQL(sql);
        sql = "delete from method where method_id > 4";
        db.execSQL(sql);
        sql = "delete from business";
        db.execSQL(sql);
    }

    public static String finUidById(int id) {
        String uid = "";
        String sql = "select * from account where account_id=" + id;
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            uid = cursor.getString(cursor.getColumnIndexOrThrow("account_key"));
        }
        cursor.close();
        return uid;
    }
//    创建账本
    public static void createAccount(AccountBean accountBean) {
        ContentValues values = new ContentValues();
        values.put("account_name",accountBean.getAccount_name());
        values.put("account_time",accountBean.getAccount_time());
        values.put("account_kind",accountBean.getAccount_kind());
        values.put("account_key",accountBean.getAccount_key());
        db.insert("account",null,values);
        Log.i("succeed","account_name: "+accountBean.getAccount_name()+"   account_time: "+accountBean.getAccount_time()
                +"   account_kind: "+accountBean.getAccount_kind()+"   account_key: "+accountBean.getAccount_key()+"插入数据成功！！！");
    }

    public static AccountBean getAccountById(int selectAccountId) {
        AccountBean accountBean = new AccountBean();
        String sql = "select * from account where account_id ="+selectAccountId;
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndexOrThrow("account_id"));
            String name = cursor.getString(cursor.getColumnIndexOrThrow("account_name"));
            Log.i("accountName",name+"");
            float outcome = 0;
            float income = 0;
            Cursor sqloutcome = db.rawQuery("select sum(consumption_money) from consumption where consumption_kind=0 and account_id='" + id + "'",null);
            while (sqloutcome.moveToNext()){
                outcome = sqloutcome.getFloat(sqloutcome.getColumnIndexOrThrow("sum(consumption_money)"));
            }
            sqloutcome.close();
            Cursor sqlincome = db.rawQuery("select sum(consumption_money) from consumption where consumption_kind=1 and account_id='" + id + "'",null);
            while (sqlincome.moveToNext()){
                income = sqlincome.getFloat(sqlincome.getColumnIndexOrThrow("sum(consumption_money)"));
            }
            sqlincome.close();
            int item = 0;
            Cursor sqlItem = db.rawQuery("select * from consumption where account_id='"+id+"'",null);
            while (sqlItem.moveToNext()){
                item++;
            }
            sqlItem.close();
            Log.i("item",item+"");
            String time = cursor.getString(cursor.getColumnIndexOrThrow("account_time"));
            int kind=cursor.getInt(cursor.getColumnIndexOrThrow("account_kind"));
            String key = cursor.getString(cursor.getColumnIndexOrThrow("account_key"));
//            Log.i("account_key",key+"");
            accountBean.setAccount_id(id);
            accountBean.setAccount_name(name);
            accountBean.setAccount_outcome(outcome);
            accountBean.setAccount_income(income);
            accountBean.setAccount_item(item);
            accountBean.setAccount_time(time);
            accountBean.setAccount_kind(kind);
            accountBean.setAccount_key(key);
//            Log.i("key",accountBean.getAccount_key()+"");
        }
        cursor.close();
        return accountBean;
    }

    public static List<ConsumptionBean> findConsumptionByKey() {
        List<ConsumptionBean> consumptionBeanList = new ArrayList<>();
        String sql = "select * from consumption where consumption_key=0";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            int consumption_id = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_id"));
            String consumption_class_name = cursor.getString(cursor.getColumnIndexOrThrow("consumption_class_name"));
            String consumption_remarks = cursor.getString(cursor.getColumnIndexOrThrow("consumption_remarks"));
            float consumption_money = cursor.getFloat(cursor.getColumnIndexOrThrow("consumption_money"));
            String consumption_business = cursor.getString(cursor.getColumnIndexOrThrow("consumption_business"));
            String account_id = cursor.getString(cursor.getColumnIndexOrThrow("account_id"));
            String consumption_method = cursor.getString(cursor.getColumnIndexOrThrow("consumption_method"));
            String consumption_time = cursor.getString(cursor.getColumnIndexOrThrow("consumption_time"));
            int consumption_year = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_year"));
            int consumption_month = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_month"));
            int consumption_day = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_day"));
            int consumption_kind = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_kind"));
            int consumption_key = cursor.getInt(cursor.getColumnIndexOrThrow("consumption_key"));
            ConsumptionBean consumptionBean = new ConsumptionBean(consumption_id,consumption_class_name,consumption_remarks,consumption_money,consumption_business,account_id,consumption_method,consumption_time,consumption_year,consumption_month,consumption_day,consumption_kind,consumption_key);
            consumptionBeanList.add(consumptionBean);
        }
        cursor.close();
        return consumptionBeanList;
    }

    public static String getAccountUid(String account_id) {
        String accountUId = "";
        String sql = "select * from account where account_id='"+account_id+"'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            accountUId = cursor.getString(cursor.getColumnIndexOrThrow("account_key"));
        }
        Log.i("accountUId",accountUId);
        cursor.close();
        return accountUId;
    }

    public static void setConsumptionKey(ConsumptionBean consumptionBean) {
        String sql = "update consumption set consumption_key = " + consumptionBean.getConsumption_key() +
                " where consumption_id = " + consumptionBean.getConsumption_id();
        db.execSQL(sql);
    }

    public static String getAccountId(String accountUid) {
        String accountId = "";
        String sql = "select * from account where account_key='"+accountUid+"'";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()){
            accountId = cursor.getString(cursor.getColumnIndexOrThrow("account_id"));
        }
        Log.i("accountId",accountId);
        cursor.close();
        return accountId;
    }

    public static void clearConsumption() {
        String sql = "delete from consumption";
        db.execSQL(sql);
    }
}